package com.bum.guru.keeng.dao;

import java.sql.SQLException;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;

import com.bum.guru.exp.keeng.om.KeengAlbum;
import com.bum.guru.exp.keeng.om.KeengSong;

public class KeengAlbumDAO {
	Logger								log					= Logger.getLogger(getClass());

	QueryRunner							queryRunner;

	ResultSetHandler<KeengAlbum>		beanHandler			= new BeanHandler<KeengAlbum>(KeengAlbum.class);
	ResultSetHandler<List<KeengSong>>	listSongsHandler	= new BeanListHandler<KeengSong>(KeengSong.class);
	ResultSetHandler<List<KeengAlbum>>	listAlbumsHandler	= new BeanListHandler<KeengAlbum>(KeengAlbum.class);

	public void add(KeengAlbum album) throws Exception {
		queryRunner.update("REPLACE INTO K_ALBUM(ID, TITLE, ARTIST, CAT, COVER, DAY) VALUES (?, ?, ?, ?, ?, ?)",
				album.getId(), album.getTitle(), album.getArtist(), album.getCat(), album.getCover(), System.currentTimeMillis());

		addSongs(album.getSongs());
	}

	final String	albumQuery	= "SELECT ID AS id, TITLE AS title, ARTIST AS artist, CAT AS cat, COVER AS cover, DAY AS day FROM K_ALBUM ";

	public KeengAlbum get(String id) {
		try {
			return queryRunner.query(albumQuery + "WHERE ID=?", beanHandler, id);
		} catch (Exception e) {
			log.debug(e.getMessage(), e);
		}
		return null;
	}

	public List<KeengAlbum> listNew(long fromMs, int maxResults) {
		try {
			if (fromMs == 0) {
				return queryRunner.query(albumQuery + " ORDER BY DAY DESC LIMIT ? OFFSET 0", listAlbumsHandler, maxResults);
			}
			return queryRunner.query(albumQuery + " WHERE DAY<? ORDER BY DAY DESC LIMIT ? OFFSET 0", listAlbumsHandler, fromMs, maxResults);
		} catch (Exception e) {
			log.debug(e.getMessage(), e);
			return null;
		}
	}

	public KeengAlbum getFull(String id) {
		KeengAlbum album = null;
		try {
			album = queryRunner.query("SELECT ID AS id, TITLE AS title, ARTIST AS artist, CAT AS cat, COVER AS cover FROM K_ALBUM WHERE ID=?",
					beanHandler, id);
		} catch (Exception e) {
			log.debug(e.getMessage(), e);
		}

		if (album == null) {
			return null;
		}

		album.setSongs(listAlbumSongs(id));

		return album;
	}

	public List<KeengSong> listAlbumSongs(String albumId) {
		String query = "SELECT ID AS id, TITLE AS title, ARTIST AS artist, ALBUM AS album, STREAM AS stream, CAT AS cat FROM K_SONG ";
		try {
			return queryRunner.query(query + " WHERE ALBUM=?", listSongsHandler, albumId);
		} catch (Exception e) {
			log.debug(e.getMessage(), e);
		}
		return null;
	}

	private void addSongs(List<KeengSong> songs) {
		for (KeengSong song : songs) {
			try {
				queryRunner.update("REPLACE INTO K_SONG(ID, TITLE, ARTIST, ALBUM, STREAM, CAT) VALUES(?, ?, ?, ?, ?, ?)",
						song.getId(), song.getTitle(), song.getArtist(), song.getAlbumId(), song.getStream(), song.getCat());

				if (StringUtils.isNotBlank(song.getLyric())) {
					queryRunner.update("REPLACE INTO K_LYRIC(ID, LYRIC) VALUES(?, ?)",
							song.getId(), song.getLyric());
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}

		}

	}

	public void setQueryRunner(QueryRunner queryRunner) {
		this.queryRunner = queryRunner;
	}
}
